package com.hisense.proxy.service.ncc.zndz;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import com.alibaba.fastjson.JSON;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import com.alibaba.excel.EasyExcel;
import com.hisense.proxy.busitrans.vo.zndz.StatementExcelNameVO;
import com.hisense.proxy.busitrans.vo.zndz.StatementExcelRecordVO;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;


/**
 * <p>
 *
 * </p>
 *
 * @author tangxqa
 * @since 2021/12/21 2:18 下午
 */
@Service
@Slf4j
public class FtpExcelFetchService {

	
	private static final String TEMP_EXCEL_PATH = System.getProperty("user.dir") + File.separator + "temp_excels" + File.separator;
	

    @SuppressWarnings("deprecation")
	@SneakyThrows
    public List<StatementExcelNameVO> connectAndListFiles(String merchantNo, String ftpUrl, String ftpUser, String ftpPwd) {


        List<StatementExcelNameVO> excels = new ArrayList<StatementExcelNameVO>();

        FTPClient ftpClient = new FTPClient();
        ftpClient.setControlEncoding("UTF-8");
        ftpClient.setConnectTimeout(10000);
        if(!ftpClient.isConnected()){
        	ftpClient.connect(ftpUrl, 21);
        	}
        int reply = ftpClient.getReplyCode();

        if (!FTPReply.isPositiveCompletion(reply)) {
            ftpClient.disconnect();
        }

        try {

            ftpClient.login(ftpUser, ftpPwd);
            ftpClient.enterLocalPassiveMode();
            ftpClient.changeWorkingDirectory("/build");
            ftpClient.setFileType(FTP.BINARY_FILE_TYPE);
            FTPFile[] files = ftpClient.listFiles();

            for (FTPFile file : files) {
                String fileName = file.getName();
                boolean isExcel = fileName.endsWith(".xls") || fileName.endsWith(".xlsx");
                if (isExcel) {
                    String _fileName = fileName.replace(".xlsx", "").replace(".xls", "");
                    String[] splits = _fileName.split("_");
                    if (splits.length != 3) {
                        continue;
                    }
                    String dateStr = splits[1];
                    String payType = splits[2];
                    StatementExcelNameVO nameVO = new StatementExcelNameVO();

                    nameVO.setFileName(fileName);

                    String pattern = "^[0-9]+$";
                    Pattern r = Pattern.compile(pattern);
//                    Matcher m = r.matcher(merchantNo);
//                    if (m.matches()) {
//                    } else {
//                        break;
//                    }

                    Matcher m = r.matcher(dateStr);
                    if (m.matches()) {
                        nameVO.setDate(dateStr);
                    } else {
                        continue;
                    }
                    m = r.matcher(payType);
                    if (m.matches()) {
                        nameVO.setPayType(payType);
                    } else {
                        continue;
                    }

                    // 该字段为商户号
                    nameVO.setMerchantNo(merchantNo);
                    
                    File tempExcelFile = new File(TEMP_EXCEL_PATH + merchantNo + File.separator + fileName);
                    tempExcelFile.getParentFile().mkdirs();
                    
                    if (! tempExcelFile.exists()) {
                    	
                    	FileOutputStream fileOutputStream = new FileOutputStream(tempExcelFile);
                        try {
    						ftpClient.retrieveFile(fileName, fileOutputStream);
    					} catch(Exception e) {
    						log.error(" {} - {} - {} - {} fetch error", merchantNo, ftpUrl, ftpUser, ftpPwd, e);
    						continue;
    					} finally {
    						fileOutputStream.flush();
    						fileOutputStream.close();
    					}
                    }


                    List<StatementExcelRecordVO> statementExcelRecordVOS = parseExcelContent(tempExcelFile);
                    if (statementExcelRecordVOS == null || statementExcelRecordVOS.size() == 0) {
                        continue;
                    }

                    nameVO.setExcelContent(statementExcelRecordVOS);
                    excels.add(nameVO);
                }
            }

        } finally {
            ftpClient.logout();
            if (ftpClient.isConnected()) {
                ftpClient.disconnect();
            }
        }
        return excels;
    }


    private List<StatementExcelRecordVO> parseExcelContent(File tempExcelFile) {
        int minHeadRowNum = 2;
        int maxHeadRowNum = 4;

        List<StatementExcelRecordVO> excelContent = null;
        for (int headRowNum=minHeadRowNum; headRowNum<=maxHeadRowNum;headRowNum++) {
            excelContent = EasyExcel.read(tempExcelFile)
                    .head(StatementExcelRecordVO.class)
                    .headRowNumber(headRowNum)
                    .sheet().doReadSync();

            if (excelContent == null) {
                continue;
            }

            excelContent = excelContent.stream().filter(item -> !StringUtils.isEmpty(item.getTrans_ref_num()) || !StringUtils.isEmpty(item.getWxali_trans_ref_num())).collect(Collectors.toList());

            if (excelContent.size() == 0) {
                continue;
            }


            // excel size > 0
            excelContent.forEach(item -> {
                if (StringUtils.isEmpty(item.getTrans_ref_num())) {
                    item.setTrans_ref_num(item.getWxali_trans_ref_num());
                }
            });

            log.info("Fetch excel content size {} from {} with headRowNum {}", excelContent.size(), tempExcelFile.getName(), headRowNum);

            break;
        }

        return excelContent;
    }


    public static void main(String[] args) {
        File excelDir = new File("/Users/tangxqa/Library/Containers/com.tencent.xinWeChat/Data/Library/Application Support/com.tencent.xinWeChat/2.0b4.0.9/d69a3fa18630db5b805b5de47e944665/Message/MessageTemp/9e20f478899dc29eb19741386f9343c8/File/tangxiaoqing/89837011520DEZ9");
        File[] files = excelDir.listFiles();
        for (File excelFile : files) {
            List<StatementExcelRecordVO> statementExcelRecordVOS = new FtpExcelFetchService().parseExcelContent(excelFile);
            log.info("{} - {}", excelFile.getName(), JSON.toJSONString(statementExcelRecordVOS));
        }
    }

}
